跳到主要内容

Database API

frappe.db.get_list ​

frappe.db.get_list(doctype, filters, or_filters, fields, order_by, group_by, start, page_length)
  • Also aliased to frappe.get_list

Returns a list of records from a doctype table. ORM Wrapper for a SELECT query. Will also apply user permissions for the records for the session user. Only returns the document names if the fields keyword argument is not given. By default this method returns a list of dicts, but, you can pluck a particular field by giving the pluck keyword argument:

frappe.db.get_list('Employee')

# output
[{'name': 'HR-EMP-00008'},
 {'name': 'HR-EMP-00006'},
 {'name': 'HR-EMP-00010'},
 {'name': 'HR-EMP-00005'}
]

# with pluck
frappe.db.get_list('Employee', pluck='name')

# output
['HR-EMP-00008',
 'HR-EMP-00006',
 'HR-EMP-00010',
 'HR-EMP-00005'
]

Combining filters and other arguments:

frappe.db.get_list('Task',
    filters={
        'status': 'Open'
    },
    fields=['subject', 'date'],
    order_by='date desc',
    start=10,
    page_length=20,
    as_list=True
)

# output
(('Update Branding and Design', '2019-09-04'),
('Missing Documentation', '2019-09-02'),
('Fundraiser for Foundation', '2019-09-03'))

# Tasks with date after 2019-09-08
frappe.db.get_list('Task', filters={
    'date': ['>', '2019-09-08']
})

# Tasks with date between 2020-04-01 and 2021-03-31 (both inclusive)
frappe.db.get_list('Task', filters=[[
    'date', 'between', ['2020-04-01', '2021-03-31']
]])

# Tasks with subject that contains "test"
frappe.db.get_list('Task', filters={
    'subject': ['like', '%test%']
})

# Count number of tasks grouped by status
frappe.db.get_list('Task',
    fields=['count(name) as count', 'status'],
    group_by='status'
)
# output
[{'count': 1, 'status': 'Working'},
 {'count': 2, 'status': 'Overdue'},
 {'count': 2, 'status': 'Open'},
 {'count': 1, 'status': 'Filed'},
 {'count': 20, 'status': 'Completed'},
 {'count': 1, 'status': 'Cancelled'}]

frappe.db.get_all ​

frappe.db.get_all(doctype, filters, or_filters, fields, order_by, group_by, start, page_length)
  • Also aliased to frappe.get_all

Same as frappe.db.get_list but will fetch all records without applying permissions.

frappe.db.get_value ​

frappe.db.get_value(doctype, name, fieldname)` or `frappe.db.get_value(doctype, filters, fieldname)
  • Also aliased to frappe.get_value and frappe.db.get_values

Returns a document's field value or a list of values.

# single value
subject = frappe.db.get_value('Task', 'TASK00002', 'subject')

# multiple values
subject, description = frappe.db.get_value('Task', 'TASK00002', ['subject', 'description'])

# as dict
task_dict = frappe.db.get_value('Task', 'TASK00002', ['subject', 'description'], as_dict=1)
task_dict.subject
task_dict.description

# with filters, will return the first record that matches filters
subject, description = frappe.db.get_value('Task', {'status': 'Open'}, ['subject', 'description'])

frappe.db.get_single_value ​

frappe.db.get_single_value(doctype, fieldname)

Returns a field value from a Single DocType.

timezone = frappe.db.get_single_value('System Settings', 'timezone')

frappe.db.set_value ​

frappe.db.set_value(doctype, name, fieldname, value)
  • Also aliased to frappe.db.update

Sets a field's value in the database, does not call the ORM triggers but updates the modified timestamp (unless specified not to).

# update a field value
frappe.db.set_value('Task', 'TASK00002', 'subject', 'New Subject')

# update multiple values
frappe.db.set_value('Task', 'TASK00002', {
    'subject': 'New Subject',
    'description': 'New Description'
})

# update without updating the `modified` timestamp
frappe.db.set_value('Task', 'TASK00002', 'subject', 'New Subject', update_modified=False)

This method won't call ORM triggers like validate and on_update. Use this method to update hidden fields or if you know what you are doing.

frappe.db.exists ​

frappe.db.exists(doctype, name)

Returns true if a document record exists.

Pass doctype and docname:

frappe.db.exists("User", "[email protected]", cache=True)

Pass a dict of filters including the "doctype" key:

frappe.db.exists({"doctype": "User", "full_name": "Jane Doe"})

Pass the doctype and a dict of filters:

frappe.db.exists("User", {"full_name": "Jane Doe"})

frappe.db.count ​

frappe.db.count(doctype, filters)

Returns number of records for a given doctype and filters.

# total number of Task records
frappe.db.count('Task')

# total number of Open tasks
frappe.db.count('Task', {'status': 'Open'})

frappe.db.delete ​

frappe.db.delete(doctype, filters)

Delete doctype records that match filters. This runs a DML command, which means it can be rolled back. If no filters specified, all the records of the doctype are deleted.

frappe.db.delete("Route History", {
    "modified": ("<=", last_record_to_keep[0].modified),
    "user": user
})

frappe.db.delete("Error Log")
frappe.db.delete("__Test Table")

You may pass the doctype name or an internal table name. Conventionally, internal tables in Frappe are prefixed with __. The API follows this. The above commands run an unconditional DELETE query over tables tabError Log and __Test Table.

frappe.db.truncate ​

frappe.db.truncate(doctype)

Truncate a table in the database. This runs a DDL command TRUNCATE TABLE, a commit is triggered before the statement is executed. This action cannot be rolled back. You may want to use this for clearing out log tables periodically.

frappe.db.truncate("Error Log")
frappe.db.truncate("__Test Table")

The above commands run a TRUNCATE query over tables tabError Log and __Test Table.

frappe.db.commit ​

frappe.db.commit()

Commits current transaction. Calls SQL COMMIT.

In most cases you don't need to commit manually. Refer Frappe's Database transaction model below.

frappe.db.savepoint ​

frappe.db.savepoint(save_point)

Create a named savepoint to which you can later roll back to.

frappe.db.rollback ​

frappe.db.rollback()

Rollbacks current transaction. Calls SQL ROLLBACK.

Frappe will automatically run frappe.db.rollback() if an exception is thrown during a Web Request of type POST or PUT. Use this if you have to rollback early in a transaction.

frappe.db.rollback(save_point="save_point_name")

Rollback to a specific savepoint instead rolling back full transactions. This rollback won't undo changes done to filesytem and any other rollback watchers.

frappe.db.sql ​

frappe.db.sql(query, values, as_dict)

Execute an arbitrary SQL query. This may be useful for complex server side reports with join statements, adjusting the database to new features, etc.

Example:

values = {'company': 'Frappe Technologies Inc'}
data = frappe.db.sql("""
    SELECT
        acc.account_number
        gl.debit
        gl.credit
    FROM `tabGL Entry` gl
        LEFT JOIN `tabAccount` acc
        ON gl.account = acc.name
    WHERE gl.company = %(company)s
""", values=values, as_dict=0)

Avoid using this method as it will bypass validations and integrity checks. It's always better to use frappe.get_doc, frappe.db.get_list, etc., if possible.

frappe.db.multisql ​

frappe.db.multisql({'mariadb': mariadb_query, 'postgres': postgres_query})

Execute the suitable SQL statement for any supported database engine.

frappe.db.rename_table ​

frappe.db.rename_table(old_name, new_name)

Executes a query to change table name. Specify the DocType or internal table's name directly to rename the table.

Example:

frappe.db.rename_table("__internal_cache", "__temporary_cache")
frappe.db.rename_table("todo", "ToDo")

The second example should be used only if you understand the ramifications of it.

Don't use this to rename DocType tables. Use frappe.rename_doc for that instead

frappe.db.describe ​

frappe.db.describe(doctype)

Returns a tuple of the table description for given DocType.

frappe.db.change_column_type ​

frappe.db.change_column_type(doctype, column, new_type)

Changes the type of column for specified DocType.

frappe.db.add_index ​

frappe.db.add_index(doctype, fields, index_name)

Creates indexes for doctypes for the specified fields.

Note: if you want an index on a TEXT or a BLOB field, you must specify a fixed length to do that.

Example:

frappe.db.add_index("Notes", ["id(10)", "content(500)"], index_name)

frappe.db.add_unique ​

frappe.db.add_unique(doctype, fields, constraint_name=None)

Creates unique constraint for doctypes for the specified fields.

Example:

frappe.db.add_unique("DoctypeName",["field1","field2"])

Database transaction hooks ​

Note: This API is introduced in v15.

Frappe provides hooks for running callbacks before/after transaction commands like commit/rollback are issued. These hooks are useful for:

  • Rolling back changes that are done outside of the database if the transaction is rolled back
  • Flushing changes outside of the database only if the transaction is committed.

These hooks are :

  • frappe.db.before_commit.add(func: Callable)
  • frappe.db.after_commit.add(func: Callable)
  • frappe.db.before_rollback.add(func: Callable)
  • frappe.db.after_rollback.add(func: Callable)

Example usage:

def create_file(self):
    self.write_file()
    # This ensures rollback if DB transaction is rolledback
    frappe.db.after_rollback.add(self.rollback_file)

def rollback_file(self):
    self.delete_file()

Database transaction model ​

Frappe's database abstractions implement a sane transaction model by default. So in most cases, you won't have to deal with SQL transactions manually. A broad description of this model is described below:

Web requests ​

  • While performing POST or PUT, if any writes were made to the database, they are committed at end of the successful request.
  • AJAX calls made using frappe.call are POST by default unless changed.
  • GET requests do not cause an implicit commit.
  • Any uncaught exception during handling of request will rollback the transaction.

Background/scheduled Jobs ​

  • Calling a function as background or scheduled job will commit the transaction after successful completion.
  • Any uncaught exception will cause rollback of the transaction.

Patches ​

  • Successful completion of the patch's execute function will commit the transaction automatically.
  • Any uncaught exception will cause rollback of the transaction.

Unit tests ​

  • Transaction is committed after running one test module. Test module means any python test file like test_core.py.
  • Transaction is also committed after finishing all tests.
  • Any uncaught exception will exit the test runner, hence won't commit.

Note: If you're catching exceptions anywhere, then database abstraction does not know that something has gone wrong hence you're responsible for the correct rollback of the transaction.